/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import beans.CustomerBean;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author quangphamngoc
 */
public class CustomerDB {
    private static Connection con;
    private static Statement stmt;
    private static PreparedStatement pstmt;
    private static CallableStatement cstmt;
    private static ResultSet rs;
    public static int error = 0;

    public static boolean connect() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your MySQL JDBC Driver?");
            e.printStackTrace();
            error = 2;
            return false;

        }
        // TODO code application logic here
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:" + ConfigDB.DBPORT + "/" + ConfigDB.DBNAME, ConfigDB.DBUSER, ConfigDB.DBPASS);

        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            //Co the do sai ten db
            error = 1;
            e.printStackTrace();
            return false;
        }

        if (con != null) {
            System.out.println("You made it, take control your database now!");
            return true;
        } else {
            System.out.println("Failed to make connection!");
            return false;
        }
    }

    public static void disconnect() {
        try {
            con.close();
        } catch (SQLException ex) {
            error = 1;
            System.out.println(ex);
            ex.printStackTrace();
        }
    }

    public static List<CustomerBean> getAllCustomers() {
        List<CustomerBean> list = new ArrayList<CustomerBean>();
        try {
            if (!connect()) {
                return list;
            } else {
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT * FROM Customers");
                while (rs.next()) {
                    CustomerBean item = new CustomerBean();
                    item.setCus_ID(rs.getInt("Cus_ID"));
                    item.setCus_FirstName(rs.getString("Cus_FirstName"));
                    item.setCus_LastName(rs.getString("Cus_LastName"));
                    item.setCus_MiddleName(rs.getString("Cus_MiddleName"));
                    item.setCus_City(rs.getString("Cus_City"));
                    item.setCus_HouseNo(rs.getString("Cus_HouseNo"));
                    item.setCus_CCNumber(rs.getString("Cus_CCNumber"));
                    item.setCus_Phone(rs.getString("Cus_Phone"));
                    item.setCus_Status(rs.getInt("Cus_Status"));
                    item.setCus_Street(rs.getString("Cus_Street"));
                    list.add(item);
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
        return list;
    }
    
    public static CustomerBean getCustomerByID(int id) {
        CustomerBean item = new CustomerBean();
        try {
            if (!connect()) {
                
            } else {
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT * FROM Customers WHERE CustomerID = " + id);
                while (rs.next()) {
                    item.setCus_ID(rs.getInt("Cus_ID"));
                    item.setCus_FirstName(rs.getString("Cus_FirstName"));
                    item.setCus_LastName(rs.getString("Cus_LastName"));
                    item.setCus_MiddleName(rs.getString("Cus_MiddleName"));
                    item.setCus_City(rs.getString("Cus_City"));
                    item.setCus_HouseNo(rs.getString("Cus_HouseNo"));
                    item.setCus_CCNumber(rs.getString("Cus_CCNumber"));
                    item.setCus_Phone(rs.getString("Cus_Phone"));
                    item.setCus_Status(rs.getInt("Cus_Status"));
                    item.setCus_Street(rs.getString("Cus_Street"));
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
        return item;
    }
    
    public static boolean editCustomer(CustomerBean item) {
        boolean success = false;
        try {
            if (!connect()) {

            } else {
                pstmt = con.prepareStatement("UPDATE `SuperMarket`.`Customers` SET `Cus_FirstName`='Pham', `Cus_MiddleName`='Quang', `Cus_LastName`='Ngoc', `Cus_HouseNo`='200', `Cus_Street`='Minh KHai', `Cus_City`='Vinh', `Cus_Phone`='1234', `Cus_CCNumber`='123', `Cus_CCExpiry`='123', `Cus_Status`='0' WHERE `Cus_ID`='1';");
//                pstmt.setString(1, item.getCat_Name());
//                pstmt.setInt(2, item.getCat_Status());
//                pstmt.setInt(3, item.getCat_ID());

                int kq = pstmt.executeUpdate();
                if (kq != 0) {
                    success = true;
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(CustomerDB.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
        return success;
    }
    //INSERT INTO `SuperMarket`.`Customers` (`Cus_FirstName`, `Cus_MiddleName`, `Cus_LastName`, `Cus_HouseNo`, `Cus_Street`, `Cus_City`, `Cus_Phone`, `Cus_Status`) VALUES ('Quang', 'Ngoc', 'Pham', '133', 'Nguyen Trai', 'Ha Noi', '01692677217', '1');
}
